<?php
namespace app\controllers\crontab;
use Yii;
use app\components\EmailMarket;
class ManualDealChannelStatisticsDataController extends \app\base\Controller 
{
    
    /**
     * 初始化
     */
    public function init()
    {
        yii::$app->db->enableSlaves = false;        // 禁用从库查询，从主库查询数据。
    }
    
    /**
     * 更改队列信息的默认状态
     */
    public function actionUpdateListSendEmarsysStatus()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $siteArr = Yii::$app->params['sites']['marketing_email']['web_index'];
        $curTime =  \yii::$app->bjTime->time();
       
        $dayStartTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",$curTime)." 00:00:00");
        $startTime30Ago =  $dayStartTime-3600*24*30;
        foreach ($siteArr as $siteName) {
            $curEmailListTable = 'mail_sendlist_' . $siteName;
            
            // 把一天之前30天之内的发送的未统计的数据数量查询出来
            $updateSql = "update {$curEmailListTable} set send_status=1 where last_send<{$dayStartTime} and last_send>{$startTime30Ago} and state=2 and send_status=0 and (channel_id=2 or channel_id=6)"; // 把剩余的数据的发送状态置为成功
            // 更新数据
            Yii::$app->db->createCommand($updateSql)->execute();
           
        }
    }
    
    /**
     * 更改队列信息的默认状态
     */
    public function actionUpdateListSendCheetStatus()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        $siteArr = Yii::$app->params['sites']['marketing_email']['web_index'];
        $curTime =  \yii::$app->bjTime->time();
       
        $dayStartTime = \yii::$app->bjTime->strtotime(\yii::$app->bjTime->date("Y-m-d",$curTime)." 00:00:00");
        $startTime30Ago =  $dayStartTime-3600*24*30;
        foreach ($siteArr as $siteName) {
            $curEmailListTable = 'mail_sendlist_' . $siteName;
            
            // 把一天之前30天之内的发送的未统计的数据数量查询出来
            $updateSql = "update {$curEmailListTable} set send_status=2 where last_send<{$dayStartTime} and last_send>{$startTime30Ago} and state=2 and send_status=0 and channel_id=4"; // 把剩余的数据的发送状态置为成功
            // 更新数据
            Yii::$app->db->createCommand($updateSql)->execute();
           
        }
    }
    
    /**
     * 把channel_return_info_statistics表中的数据统计到
     * mail_group_email_type_statistics中
     */ 
    public function actionDataToTypeStatistics()
    {
        // 每个gid和email_type只有一条数据
        set_time_limit(0);
        $numbers = 3000;// 每次查询的数量
        $tableName = "channel_return_info_statistics";
        $sql = "select * from {$tableName} where is_deal=0 limit {$numbers}";
        $allInfo = Yii::$app->db->createCommand($sql)->queryAll();
        $host = Yii::$app->getHomeUrl() ? Yii::$app->getHomeUrl() : "http://ems.appinthestore.com/";
        if($allInfo)
        {
            
            $curBjTime = \yii::$app->bjTime->time();
            $idArr = [];
            $idStr = "";
            foreach ($allInfo as $allInfoV) {
                $idArr[] = $allInfoV['id'];
                $selectSql = "select count(*) from mail_group_email_type_statistics where gid={$allInfoV['gid']} and email_type={$allInfoV['email_type']}";
                $gidType = Yii::$app->db->createCommand($selectSql)->queryScalar();
                $sendFail = $allInfoV['hard_bounces_nums'] + $allInfoV['soft_bounces_nums'];
                if ($gidType) {

                    // 更新数据
                    $updateSql = "update mail_group_email_type_statistics set send_success=send_success+{$allInfoV['send_success_nums']},send_fail=send_fail+{$sendFail},click_nums=click_nums+{$allInfoV['click_nums']} where  gid={$allInfoV['gid']} and email_type={$allInfoV['email_type']}";
                    Yii::$app->db->createCommand($updateSql)->execute();
                } else {

                    // 插入数据
                    $insertSql = "insert into mail_group_email_type_statistics(gid,email_type,send_success,send_fail,click_nums,update_time) values ({$allInfoV['gid']},{$allInfoV['email_type']},{$allInfoV['send_success_nums']},{$sendFail},{$allInfoV['click_nums']},{$curBjTime})";
                    Yii::$app->db->createCommand($insertSql)->execute();
                }

                // 更新分组表中的数据
                if ($allInfoV['gid']) {
                    $updateGroupSql = "update mail_group set send_status_ok_count=send_status_ok_count+{$allInfoV['send_success_nums']},send_status_fail_count=send_status_fail_count+{$sendFail},click_link_count=click_link_count+{$allInfoV['click_nums']} where group_id = {$allInfoV['gid']}";
                    Yii::$app->db->createCommand($updateGroupSql)->execute();
                }

            }
            if ($idArr) {
                $idStr = join(',',$idArr);
                $updateSqlDeal = "update {$tableName} set is_deal = 1 where id in({$idStr})";
                Yii::$app->db->createCommand($updateSqlDeal)->execute();
            }
            echo "<script type='text/javascript'>location.href='".($host.'crontab/manual-deal-channel-statistics-data/data-to-type-statistics?site=marketing_email')."';</script>";
//            return $this->redirect($host.'crontab/manual-deal-channel-statistics-data/data-to-type-statistics?site=marketing_email');
            exit;
        }
        
       
        echo '处理完毕';
    }
    
     /*
     *定时同步统计临时表数据到正式表中 
     */
    public function actionSynStatisticsTemp() {
        
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $channelType = Yii::$app->request->get('channelType') ? Yii::$app->request->get('channelType') : 1;//1是emarsys和emarsys_new通道，2是cheet通道
        
        if ($channelType == 1) {
            $channelWhere = ' channel_id in(2,6) ';
            $channelName = 'emarsys';
        } else if($channelType == 2) {
            $channelWhere = " channel_id = 4 ";
            $channelName = "cheet";
        }
        $limit = 5000;
        $tableName = "mail_statistics_temp";
        
        //从明细表查询未被统计的数据
        $sql = "select site_id from {$tableName} where {$channelWhere} group by site_id limit 1";
        $siteIdOne = Yii::$app->db->createCommand($sql)->queryOne();
        
        
        if($siteIdOne) {
            $siteId = $siteIdOne['site_id'];
            $idsArr = $clickEmailsArr = $bouncesIdsArr = $successIdsArr = $inClickEmailArr = array();
            
            $getDataSql = "select * from {$tableName} where {$channelWhere} and site_id = $siteId limit $limit";
            
            $allDataArr = Yii::$app->db->createCommand($getDataSql)->queryAll();
            
            if($allDataArr) {
                
                $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$siteId]; 
                
                foreach($allDataArr as $allData) {
                    
                    $idsArr[] = $allData['id'];
                    
                    if($allData['is_hard_bounces'] == 1 || $allData['is_soft_bounces'] == 1) {
                        $bouncesIdsArr[] = $allData['id'];
                    }
                    if($allData['is_send_success'] == 1) {
                        $successIdsArr[] = $allData['id'];
                    }
                    if($allData['is_click'] == 1) {
                        $clickEmailsArr[$allData['email']] = \yii::$app->bjTime->strtotime($allData['click_time']);
                        $inClickEmailArr[] = "'".$allData['email']."'";
                    }
                    
                }
                if($bouncesIdsArr) {
                    $updateBouncesSql = "update mail_sendlist_{$curSiteName} m join {$tableName} t on m.group_id = t.group_id and m.email = t.email set m.send_status=2 where t.id in(".join(',',$bouncesIdsArr).")";
                    
                    Yii::$app->db->createCommand($updateBouncesSql)->execute();
                }
                
                if($successIdsArr) {
                    $updateSuccessSql = "update mail_sendlist_{$curSiteName} m join {$tableName} t on m.group_id = t.group_id and m.email = t.email set m.send_status=1 where t.id in(".join(',',$successIdsArr).")";
                    
                    Yii::$app->db->createCommand($updateSuccessSql)->execute();
                }
                
                if($clickEmailsArr) {
                    $updateClickArr = $usersArr = $insertClickArr = array();
                    $updateClickSql = "update user_{$curSiteName} set last_click_time = CASE email ";
                    $clickEmailsStr = join(',', array_unique($inClickEmailArr));
                    foreach($clickEmailsArr as $email => $clickTime) {
                        
                        $updateClickArr[] = "WHEN '{$email}' THEN ".$clickTime." ";
                    }
                    if($updateClickArr) {
                        $updateClickSql .= join('',$updateClickArr)." END where email in (".$clickEmailsStr.")";
                        // 入库
                        Yii::$app->db->createCommand($updateClickSql)->execute();
                    }
                    
                    $selectClicksql = "select user_id,email from user_{$curSiteName} where email in (".$clickEmailsStr.")";
                    
                    $userData = Yii::$app->db->createCommand($selectClicksql)->queryAll();
                    
                    if($userData) {
                        foreach($userData as $user) {
                            $usersArr[$user['email']] = $user['user_id'];
                        }
                        if($usersArr) {
                            $insertClickSql = "insert into user_{$curSiteName}_email_click_record(user_id,email_click_time) values ";
                            foreach ($usersArr as $email => $userId) {
                                if(isset($clickEmailsArr[$email])) {
                                    $insertClickArr[] = "($userId,{$clickEmailsArr[$email]})";
                                }
                                
                            }
                            
                            if($insertClickArr) {
                                $insertClickSql .= join(',',$insertClickArr);
                                // 入库
                                Yii::$app->db->createCommand($insertClickSql)->execute();
                            }
                        }
                        
                    }
                    
                    
                }
                
                //处理完成后将其删除
                if($idsArr) {
                    $deleteSql = "delete from {$tableName} where id in(".join(',',$idsArr).")";
                    // 入库
                    Yii::$app->db->createCommand($deleteSql)->execute();
                }
            }
            $host = YII_ENV_PROD ?  'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/' ;
            echo "当前批次数据处理完成！";
            echo "<script type='text/javascript'>location.href='".($host.'crontab/manual-deal-channel-statistics-data/syn-statistics-temp?site=marketing_email&channelType='.$channelType)."';</script>";
//            return $this->redirect($host.'crontab/manual-deal-channel-statistics-data/data-to-type-statistics?site=marketing_email');
            exit;
        }
        $channelName = $channelType == 1 ? 'emarsys' : "cheet";
        echo $channelName."通道数据处理完成！";
        exit;
        
    }
    
    
    
    public function actionDoData() {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $emailObj = new EmailMarket();
        $limit = 5000;
        $tableName = "mail_trace_detail";
        $sql = "select * from {$tableName} where email_type = 0 limit $limit";
        $allTraceArr = Yii::$app->db->createCommand($sql)->queryAll();
        
        if($allTraceArr) {
            $traceIds = $updateTraceArr = $updateEmailTypeArr = $updateSendSunccessArr = $updateBouncesArr = $updateComplaintArr = $updateUnsubscribeArr = $updateClickArr =  array();
            
            $updateSql = "update $tableName set ";
            foreach($allTraceArr as $allTrace) {
                $traceIds[] = $allTrace['id'];
                $emailType = $emailObj->getEmailType($emailObj->decryption($allTrace['email']));
                $sendSuccessDate = $allTrace['send_success_time'] ? strstr($allTrace['send_success_time'],' ',true) : '';
                $bouncesDate = $allTrace['bounces_time'] ? strstr($allTrace['bounces_time'],' ',true) : '';
                $complaintDate = $allTrace['complaint_time'] ? strstr($allTrace['complaint_time'],' ',true) : '';
                $unsubscribeDate = $allTrace['unsubscribe_time'] ? strstr($allTrace['unsubscribe_time'],' ',true) : '';
                $clickDate = $allTrace['click_time'] ? strstr($allTrace['click_time'],' ',true) : '';
                $updateEmailTypeArr[] = "WHEN {$allTrace['id']} THEN {$emailType} ";
                $updateSendSunccessArr[] = "WHEN {$allTrace['id']} THEN '{$sendSuccessDate}' ";
                $updateBouncesArr[] = "WHEN {$allTrace['id']} THEN '{$bouncesDate}' ";
                $updateComplaintArr[] = "WHEN {$allTrace['id']} THEN '{$complaintDate}' ";
                $updateUnsubscribeArr[] = "WHEN {$allTrace['id']} THEN '{$unsubscribeDate}' ";
                $updateClickArr[] = "WHEN {$allTrace['id']} THEN '{$clickDate}' ";
            }
            
            if($updateEmailTypeArr) {
                $updateTraceArr[] = "email_type = CASE id ".join('',$updateEmailTypeArr)." END";
            } 
            if($updateSendSunccessArr) {
                $updateTraceArr[] = "send_success_date = CASE id ".join('',$updateSendSunccessArr)." END";
            }
            if($updateBouncesArr) {
                $updateTraceArr[] = "bounces_date = CASE id ".join('',$updateBouncesArr)." END";
            }
            if($updateComplaintArr) {
                $updateTraceArr[] = "complaint_date = CASE id ".join('',$updateComplaintArr)." END";
            }
            if($updateUnsubscribeArr) {
                $updateTraceArr[] = "unsubscribe_date = CASE id ".join('',$updateUnsubscribeArr)." END";
            }
            if($updateClickArr) {
                $updateTraceArr[] = "click_date = CASE id ".join('',$updateClickArr)." END";
            }
            if($updateTraceArr) {
                
                $updateSql .= join(',',$updateTraceArr)." where id in (".join(',',$traceIds).")";
//                var_dump($updateSql);exit;
                // 入库
                Yii::$app->db->createCommand($updateSql)->execute();
            }
            $host = YII_ENV_PROD ?  'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/' ;
            echo "当前批次数据处理完成！";
            echo "<script type='text/javascript'>location.href='".($host.'crontab/manual-deal-channel-statistics-data/do-data?site=marketing_email')."';</script>";
//            return $this->redirect($host.'crontab/manual-deal-channel-statistics-data/data-to-type-statistics?site=marketing_email');
            exit;
        }
        echo "数据处理完成！";
        
    }
    
      /*
     * 从明细表统计数量到统计表中
     * @liugang 2017-11-24
     * 
     */
    public function actionStatisticsData()
    {
        ignore_user_abort(true); 
        set_time_limit(0);
        
        $channelType = Yii::$app->request->get('channelType') ? Yii::$app->request->get('channelType') : 1;//1是emarsys和emarsys_new通道，2是cheet通道
        $groupGetId = Yii::$app->request->get('groupId') ? Yii::$app->request->get('groupId') : 0;
        $curPage = Yii::$app->request->get('curPage') ? Yii::$app->request->get('curPage') : 0;
        if($curPage < 0) {
            $curPage = 0;
        }
        if($curPage > 49) {
            $curPage = 49;
        }
        if ($channelType == 1) {
            $channelWhere = ' (channel_id = 2 or channel_id = 6)';
        } else if($channelType == 2) {
            $channelWhere = " channel_id = 4 ";
        }
        
        $tableName = "mail_trace_detail";
        
        
        $perGroupIds = array();
        for($i = 0;$i<50;$i++) {
           
            $perGroupIds[$i] = array("max" => ($i+1)*75, "min" => ($i*75+1));
            
        }
        $channelWhere .= " and gid >= {$perGroupIds[$curPage]['min']} and gid <= {$perGroupIds[$curPage]['max']}";
        
        $gidArr = $groupInfoArrDeal = array();
        if($groupGetId) {
            $gidArr[] = $groupGetId;
        } else {
             //从明细表查询未被统计的分组
            $sql = "select gid from {$tableName} where is_deal=0 and {$channelWhere} group by gid desc limit 1";
            $allGidArr = Yii::$app->db->createCommand($sql)->queryAll();
            
    //        var_dump($allGidArr);exit;
            if($allGidArr)
            {
                foreach($allGidArr as $gid) {
                    $gidArr[] = $gid['gid'];
                }
            }
        }
        if ($gidArr) {
            
            $curBjTime = \yii::$app->bjTime->time();
            $gidStr = join(',',$gidArr);
            // 从分组表中查询分组名称和网站id的相关数据
            $sqlGroup = "select mg.group_id,mg.channel_id,mg.group_name,mg.site_id,mt.type_id from mail_group as mg left join mail_template as mt on mg.template_pk=mt.id where group_id in ({$gidStr})";
            $groupInfoArr = Yii::$app->db->createCommand($sqlGroup)->queryAll();
            if ($groupInfoArr) {
                foreach ($groupInfoArr as $groupInfoArrV) {
                    $groupInfoArrDeal[$groupInfoArrV['group_id']] = $groupInfoArrV; 
                }
            }
            
            foreach($gidArr as $groupId) {
                
                if(isset($groupInfoArrDeal[$groupId])) {
                    
                    //开启事务处理
                    $trans = Yii::$app->db->beginTransaction();
                    try {
                            //初始化变量
                            $curGroupTotalSuccessNums = $curGroupTotalBouncesNums = $curGroupTotalClicksNums = 0;
                            for( $i=0 ; $i < 6 ; $i++ ) {
                                $curGroupPerEmailTypeSuccessNums[$i] = 0;
                                $curGroupPerEmailTypeBouncesNums[$i] = 0;
                                $curGroupPerEmailTypeClicksNums[$i]  = 0;
                            }

                            // 根据gid获取相关的分组信息
                            $channelId = $groupInfoArrDeal[$groupId]['channel_id'];
                            $groupName = $groupInfoArrDeal[$groupId]['group_name'];
                            $siteId = $groupInfoArrDeal[$groupId]['site_id'];
                            $templateTypeId = $groupInfoArrDeal[$groupId]['type_id'];

                            $insertSuccessNumsValueArr = $insertHardBouncesNumsValueArr = $insertSoftBouncesNumsValueArr = $insertComplaintNumsValueArr = $insertUnsubscribeNumsValueArr = $insertClickNumsValueArr = array();

                            $curSiteName = Yii::$app->params['sites']['marketing_email']['web_index'][$groupInfoArrDeal[$groupId]['site_id']]; 
                            $tableNameSendList = "mail_sendlist_".$curSiteName;
                            
                            $siteNameArr = array('gearbest');
                            
                            if(in_array($curSiteName, $siteNameArr)) {
                                $tableNameSendList .= "_01";
                            }
                            
                            //统计当前分组的通道返回发送成功数量
                            $getSuccessNumsSql = "select count(DISTINCT de.gid,de.email) num,se.email_type,SUBSTRING_INDEX(de.send_success_time,' ',1) stat_date from {$tableName} de join {$tableNameSendList} se on de.gid = se.group_id and de.email = se.email where de.is_send_success = 1 and de.gid={$groupId} group by se.email_type,SUBSTRING_INDEX(de.send_success_time,' ',1)";

                            $groupSuccessInfoArr = Yii::$app->db->createCommand($getSuccessNumsSql)->queryAll();

                            $insertSuccessNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,send_success_nums,stat_date,stat_time) values ";

                            foreach($groupSuccessInfoArr as $groupSuccessInfo) {
                                $curGroupPerEmailTypeSuccessNums[$groupSuccessInfo['email_type']] +=  $groupSuccessInfo['num'];
                                $curGroupTotalSuccessNums += $groupSuccessInfo['num'];
                                $insertSuccessNumsValueArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$groupSuccessInfo['email_type']},{$templateTypeId},{$groupSuccessInfo['num']},'{$groupSuccessInfo['stat_date']}',{$curBjTime})";
                            }
                            if($insertSuccessNumsValueArr) {
                                $insertSuccessNumsSql = $insertSuccessNumsSql.join(',',$insertSuccessNumsValueArr)." ON DUPLICATE KEY UPDATE send_success_nums=values(send_success_nums),stat_time={$curBjTime}";

                                // 入库
                                Yii::$app->db->createCommand($insertSuccessNumsSql)->execute();
                            }
                            //统计当前分组的通道返回发送成功数量结束

                            //统计当前分组的通道返回发送失败（硬弹）数量
                            $getHardBouncesNumsSql = "select count(DISTINCT de.gid,de.email) num,se.email_type,SUBSTRING_INDEX(de.bounces_time,' ',1) stat_date from {$tableName} de join {$tableNameSendList} se on de.gid = se.group_id and de.email = se.email where de.is_hard_bounces = 1 and de.gid={$groupId} group by se.email_type,SUBSTRING_INDEX(de.bounces_time,' ',1)";

                            $groupHardBouncesInfoArr = Yii::$app->db->createCommand($getHardBouncesNumsSql)->queryAll();

                            $insertHardNouncesNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,hard_bounces_nums,stat_date,stat_time) values ";

                            foreach($groupHardBouncesInfoArr as $groupHardBouncesInfo) {

                                $curGroupPerEmailTypeBouncesNums[$groupHardBouncesInfo['email_type']] += $groupHardBouncesInfo['num'];
                                $curGroupTotalBouncesNums += $groupHardBouncesInfo['num'];

                                $insertHardBouncesNumsValueArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$groupHardBouncesInfo['email_type']},{$templateTypeId},{$groupHardBouncesInfo['num']},'{$groupHardBouncesInfo['stat_date']}',{$curBjTime})";
                            }

                            if($insertHardBouncesNumsValueArr) {
                                $insertHardNouncesNumsSql = $insertHardNouncesNumsSql.join(',',$insertHardBouncesNumsValueArr)." ON DUPLICATE KEY UPDATE hard_bounces_nums=values(hard_bounces_nums),stat_time={$curBjTime}";

                                // 入库
                                Yii::$app->db->createCommand($insertHardNouncesNumsSql)->execute();
                            }
                            //统计当前分组的通道返回发送失败（硬弹）数量结束

                            //统计当前分组的通道返回发送失败（软弹）数量
                            $getSoftBouncesNumsSql = "select count(DISTINCT de.gid,de.email) num,se.email_type,SUBSTRING_INDEX(de.bounces_time,' ',1) stat_date from {$tableName} de join {$tableNameSendList} se on de.gid = se.group_id and de.email = se.email where de.is_soft_bounces = 1 and de.gid={$groupId} group by se.email_type,SUBSTRING_INDEX(de.bounces_time,' ',1)";

                            $groupSoftBouncesInfoArr = Yii::$app->db->createCommand($getSoftBouncesNumsSql)->queryAll();

                            $insertSoftNouncesNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,soft_bounces_nums,stat_date,stat_time) values ";

                            foreach($groupSoftBouncesInfoArr as $groupSoftBouncesInfo) {

                                $curGroupPerEmailTypeBouncesNums[$groupSoftBouncesInfo['email_type']] += $groupSoftBouncesInfo['num'];
                                $curGroupTotalBouncesNums += $groupSoftBouncesInfo['num'];

                                $insertSoftBouncesNumsValueArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$groupSoftBouncesInfo['email_type']},{$templateTypeId},{$groupSoftBouncesInfo['num']},'{$groupSoftBouncesInfo['stat_date']}',{$curBjTime})";

                            }
                            if($insertSoftBouncesNumsValueArr) {
                                $insertSoftNouncesNumsSql = $insertSoftNouncesNumsSql.join(',',$insertSoftBouncesNumsValueArr)." ON DUPLICATE KEY UPDATE soft_bounces_nums=values(soft_bounces_nums),stat_time={$curBjTime}";
                                // 入库
                                Yii::$app->db->createCommand($insertSoftNouncesNumsSql)->execute();
                            }
                            //统计当前分组的通道返回发送失败（软弹）数量结束

                            //统计当前分组的通道返回投诉数量
                            $getComplaintNumsSql = "select count(DISTINCT de.gid,de.email) num,se.email_type,SUBSTRING_INDEX(de.complaint_time,' ',1) stat_date from {$tableName} de join {$tableNameSendList} se on de.gid = se.group_id and de.email = se.email where de.is_complaint = 1 and de.gid={$groupId} group by se.email_type,SUBSTRING_INDEX(de.complaint_time,' ',1)";

                            $groupComplaintInfoArr = Yii::$app->db->createCommand($getComplaintNumsSql)->queryAll();

                            $insertComplaintNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,complaint_nums,stat_date,stat_time) values ";

                            foreach($groupComplaintInfoArr as $groupComplaintInfo) {
                                $insertComplaintNumsValueArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$groupComplaintInfo['email_type']},{$templateTypeId},{$groupComplaintInfo['num']},'{$groupComplaintInfo['stat_date']}',{$curBjTime})";

                            }
                            if($insertComplaintNumsValueArr) {
                                $insertComplaintNumsSql = $insertComplaintNumsSql.join(',',$insertComplaintNumsValueArr)." ON DUPLICATE KEY UPDATE complaint_nums=values(complaint_nums),stat_time={$curBjTime}";
                                // 入库
                                Yii::$app->db->createCommand($insertComplaintNumsSql)->execute();
                            }
                            //统计当前分组的通道返回投诉数量结束
                            //统计当前分组的通道返回退订数量
                            $getUnsubscribeNumsSql = "select count(DISTINCT de.gid,de.email) num,se.email_type,SUBSTRING_INDEX(de.unsubscribe_time,' ',1) stat_date from {$tableName} de join {$tableNameSendList} se on de.gid = se.group_id and de.email = se.email where de.is_unsubscribe = 1 and de.gid={$groupId} group by se.email_type,SUBSTRING_INDEX(de.unsubscribe_time,' ',1)";

                            $groupUnsubscribeInfoArr = Yii::$app->db->createCommand($getUnsubscribeNumsSql)->queryAll();

                            $insertUnsubscribeNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,unsubscribe_nums,stat_date,stat_time) values ";

                            foreach($groupUnsubscribeInfoArr as $groupUnsubscribeInfo) {
                                $insertUnsubscribeNumsValueArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$groupUnsubscribeInfo['email_type']},{$templateTypeId},{$groupUnsubscribeInfo['num']},'{$groupUnsubscribeInfo['stat_date']}',{$curBjTime})";

                            }
                            if($insertUnsubscribeNumsValueArr) {
                                $insertUnsubscribeNumsSql = $insertUnsubscribeNumsSql.join(',',$insertUnsubscribeNumsValueArr)." ON DUPLICATE KEY UPDATE unsubscribe_nums=values(unsubscribe_nums),stat_time={$curBjTime}";
                                 // 入库
                                Yii::$app->db->createCommand($insertUnsubscribeNumsSql)->execute();
                            }
                            //统计当前分组的通道返回退订数量结束
                            //统计当前分组的通道返回点击数量
                            $getClickNumsSql = "select count(DISTINCT de.gid,de.email) num,se.email_type,SUBSTRING_INDEX(de.click_time,' ',1) stat_date from {$tableName} de join {$tableNameSendList} se on de.gid = se.group_id and de.email = se.email where de.is_click = 1 and de.gid={$groupId} group by se.email_type,SUBSTRING_INDEX(de.click_time,' ',1)";

                            $groupClickInfoArr = Yii::$app->db->createCommand($getClickNumsSql)->queryAll();

                            $insertClickNumsSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,click_nums,stat_date,stat_time) values ";

                            foreach($groupClickInfoArr as $groupClickInfo) {

                                $curGroupPerEmailTypeClicksNums[$groupClickInfo['email_type']] += $groupClickInfo['num'];
                                $curGroupTotalClicksNums += $groupClickInfo['num'];

                                $insertClickNumsValueArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$groupClickInfo['email_type']},{$templateTypeId},{$groupClickInfo['num']},'{$groupClickInfo['stat_date']}',{$curBjTime})";

                            }
                            if($insertClickNumsValueArr) {
                                $insertClickNumsSql = $insertClickNumsSql.join(',',$insertClickNumsValueArr)." ON DUPLICATE KEY UPDATE click_nums=values(click_nums),stat_time={$curBjTime}";
                                // 入库
                                Yii::$app->db->createCommand($insertClickNumsSql)->execute();
                            }

                            //统计当前分组的通道返回点击数量结束
                            //统计当前分组通道明细数据到mail_group_email_type_statistics表中

                            $insertSql = "insert into mail_group_email_type_statistics(gid,email_type,send_success,send_fail,click_nums,update_time) values ";
                            $insertStatisticsValuesArr = array();
                            for( $j = 0 ; $j < 6 ; $j++ ) {
                                if($curGroupPerEmailTypeSuccessNums[$j] || $curGroupPerEmailTypeBouncesNums[$j] || $curGroupPerEmailTypeClicksNums[$j]) {
                                    $insertStatisticsValuesArr[] = "({$groupId},{$j},{$curGroupPerEmailTypeSuccessNums[$j]},{$curGroupPerEmailTypeBouncesNums[$j]},{$curGroupPerEmailTypeClicksNums[$j]},{$curBjTime})";
                                }
                            }
                            if($insertStatisticsValuesArr) {
                                $insertSql = $insertSql.join(',',$insertStatisticsValuesArr)." ON DUPLICATE KEY UPDATE send_success=values(send_success),send_fail=values(send_fail),click_nums=values(click_nums),update_time={$curBjTime}";
                                // 入库
                                Yii::$app->db->createCommand($insertSql)->execute();
                            }

                            //统计当前分组通道明细数据到mail_group_email_type_statistics表中结束
                            //统计当前分组通道明细数据到mail_group表中

                            if($curGroupTotalSuccessNums || $curGroupTotalBouncesNums || $curGroupTotalClicksNums) {

                                $updateGroupSql = "update mail_group set send_status_ok_count={$curGroupTotalSuccessNums},send_status_fail_count={$curGroupTotalBouncesNums},click_link_count={$curGroupTotalClicksNums} where group_id = {$groupId}";
                                // 入库
                                Yii::$app->db->createCommand($updateGroupSql)->execute();
                            }


                            //统计当前分组通道明细数据到mail_group表中结束

                            //处理当前分组的点击数据

                            $updateClickSql = "update user_{$curSiteName} u join {$tableName} t on u.email = t.email set u.last_click_time = UNIX_TIMESTAMP(t.click_time) where t.is_click = 1 and t.gid = {$groupId}";

                            Yii::$app->db->createCommand($updateClickSql)->execute();
                            //写入点击明细数据前先删除掉之前的数据
                            $deleteClickSql = "delete r from user_{$curSiteName}_email_click_record r,user_{$curSiteName} u,{$tableName} t where r.user_id = u.user_id and u.email = t.email and t.is_click = 1 and t.gid = {$groupId}";                    

                            Yii::$app->db->createCommand($deleteClickSql)->execute();

                            $insertClickSql = "insert into user_{$curSiteName}_email_click_record(user_id,email_click_time) select u.user_id,UNIX_TIMESTAMP(t.click_time) from user_{$curSiteName} u join {$tableName} t on u.email = t.email where t.is_click = 1 and t.gid = {$groupId}";

                            Yii::$app->db->createCommand($insertClickSql)->execute();
                            //处理当前分组的点击数据结束

                            //处理当前分组的失败和成功数据

                            $updateEmailListBouncesSql = "update mail_sendlist_{$curSiteName} m join {$tableName} t on m.email = t.email set m.send_status=2 where t.gid={$groupId} and m.group_id={$groupId} and (t.is_hard_bounces = 1 or t.is_soft_bounces = 1) and m.send_status=0 ";
                            Yii::$app->db->createCommand($updateEmailListBouncesSql)->execute();

                            //cheet通道只返回成功的数量
                            if($channelType == 2) {
                                $updateEmailListSuccessSql = "update mail_sendlist_{$curSiteName} m join {$tableName} t on m.email = t.email set m.send_status=1 where t.gid={$groupId} and m.group_id={$groupId} and t.is_send_success = 1 and m.send_status=0 ";
                                Yii::$app->db->createCommand($updateEmailListSuccessSql)->execute();
                            }
                            //处理当前分组的失败和成功数据结束
                            
                            //处理本地数据
                            
                            $successDataArr = $failDataArr = $insertDataArr = array();

        
                            $sqlGetSuccessStat = "select group_id, email_type, count(*) as cnt, FROM_UNIXTIME(last_send,'%Y-%m-%d') stat_date from {$tableNameSendList} where group_id = {$groupId} and state in (2,3) group by email_type,FROM_UNIXTIME(last_send,'%Y-%m-%d')";          

                            $getSuccessResults = yii::$app->db->createCommand($sqlGetSuccessStat)->queryAll();

                            foreach($getSuccessResults as $getSuccessResult) {
                                $successDataArr[$getSuccessResult['email_type']][$getSuccessResult['stat_date']] = $getSuccessResult['cnt'];
                            }

                            $sqlGetFailStat = "select group_id, email_type, count(*) as cnt, FROM_UNIXTIME(last_send,'%Y-%m-%d') stat_date from {$tableNameSendList} where group_id = {$groupId} and state = 3 group by email_type,FROM_UNIXTIME(last_send,'%Y-%m-%d')";          

                            $getFailResults = yii::$app->db->createCommand($sqlGetFailStat)->queryAll();

                            foreach($getFailResults as $getFailResult) {
                                $failDataArr[$getFailResult['email_type']][$getFailResult['stat_date']] = $getFailResult['cnt'];
                            }

                            $insertLocalSql = "insert into channel_return_info_statistics(channel_id,site_id,gid,gname,email_type,template_type_id,send_total_nums,send_fail_nums,stat_date,stat_time) values ";
                            // 更新统计表
                            foreach ($successDataArr as $email_type => $data) {

                                foreach($data as $stat_date => $nums) {

                                    $send_fail_nums = isset($failDataArr[$email_type][$stat_date]) ? $failDataArr[$email_type][$stat_date] : 0;

                                    $insertDataArr[] = "({$channelId},{$siteId},{$groupId},'{$groupName}',{$email_type},{$templateTypeId},{$nums},{$send_fail_nums},'{$stat_date}',{$curBjTime})";

                                }

                            }

                            if($insertDataArr) {

                                $insertLocalSql = $insertLocalSql.join(',',$insertDataArr)." ON DUPLICATE KEY UPDATE send_total_nums=values(send_total_nums),send_fail_nums=values(send_fail_nums),stat_time={$curBjTime}";
                                // 入库
                                Yii::$app->db->createCommand($insertLocalSql)->execute();
                            }
                            //处理本地数据结束

                            //分组处理完后更改其状态
                            $updateTraceSql = "update {$tableName} set is_deal = 1 where gid = {$groupId} and is_deal = 0";
                            Yii::$app->db->createCommand($updateTraceSql)->execute();
                            $trans->commit();
                            echo "分组{$groupId}更新数据库成功！";
                    } catch (\Exception $e) {
                            $trans->rollBack();
                            echo "分组{$groupId}更新数据库失败，回滚数据库：".$e->getMessage();
                            $this->writeLog("分组{$groupId}更新数据库失败，回滚数据库：".$e->getMessage());
                    }
                } else {
                        $updateTraceSql = "update {$tableName} set is_deal = 1 where gid = {$groupId} and is_deal = 0";
                        Yii::$app->db->createCommand($updateTraceSql)->execute();
                }
                
            }
            if(!$groupGetId) {
                $host = YII_ENV_PROD ?  'http://ems.appinthestore.com/' : 'http://a.edmlocal.com/' ;
                echo "当前批次分组处理完成！";
                echo "<script type='text/javascript'>location.href='".($host.'crontab/manual-deal-channel-statistics-data/statistics-data?site=marketing_email&channelType='.$channelType.'&curPage='.$curPage)."';</script>";
                exit;
            }
            
        }
        
        $channelName = $channelType == 1 ? 'emarsys' : "cheet";
        echo $channelName."通道数据处理完成！";
        exit;
        
    }
    
    
    /*
     * 记录相关日志
     */
    private function writeLog($msg)
    {
        $date = yii::$app->bjTime->date("Y-m-d H:i:s");
        $today = yii::$app->bjTime->date("Ymd");
        $dir = yii::$app->getRuntimePath() . "/logs/" . yii::$app->bjTime->date("Ym");
        if (!is_dir($dir)) {
            mkdir($dir, 0777, true);
        }
        $filePath = $dir . "/do_channal_data_{$today}.log";
        $msg = "[{$date}] $msg" . PHP_EOL;
        file_put_contents($filePath, $msg, FILE_APPEND);
    }
  
    
}